using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Common;


using TestMethod.Test;
namespace TestMethod.Test
{
    public class testDao123
    {

        private const string IdentityColumn = "Id";
        private const string colName = "Name";
        private const string colAge = "Age";
        private const string colBrithDay = "BrithDay";
        private const string colRemark = "Remark";
        private static readonly string[] ColumnCollections = new[] { IdentityColumn, colName, colAge, colBrithDay, colRemark };
        private readonly string _columnCollectionSelectString = string.Join(",", ColumnCollections);
        private static readonly string[] KeyColumns = new string[] { IdentityColumn };
        private readonly string WhereClause = KeyColumns.JoinFormat(" AND ", "{0} =@{0}");

        DbHelper dbDal = null;

        public testDao123()
        {
            dbDal = new DbHelper();
        }

        public testDao123(DbHelper dbDal)
        {
            this.dbDal = dbDal;
        }

        private string GetSortText(SortDefinitions sortDefinitions)
        {
            string sortDefinition;
            if (sortDefinitions != null && sortDefinitions.SoftItems.Count > 0)
            {
                sortDefinition = string.Join(" , ", sortDefinitions.SoftItems.Select(p => string.Format("{0} {1}", p.ColumnName, (p.SortOrder == SortOrder.None) ? "" : p.SortOrder.ToString())));

                sortDefinition = string.Format(" ORDER BY {0}", sortDefinition);
            }
            else
            {
                sortDefinition = "ORDER BY " + string.Join(",", KeyColumns);
            }
            return sortDefinition;
        }

        public List<test> GetPage(SortDefinitions sortDefinitions, int startIndex, int pageSize)
        {
            string sqlText;
            var sortText = GetSortText(sortDefinitions);
            if (startIndex > -1 && pageSize > 0)
            {
                sqlText = GetPagedQuery(sortText, startIndex, pageSize);
            }
            else
                sqlText = GettSelectSQL();

            var command = dbDal.GetSqlStringCommond(sqlText);
            DataTable dt = dbDal.ExecuteDataTable(command);
            return FromDt(dt);
        }

        private test From(DataRow dr)
        {
            test tmp = new test();
            if (dr[IdentityColumn] != null)
            {
                tmp.Id = (int)dr[IdentityColumn];
            }
            if (dr[colName] != null)
            {
                tmp.Name = (string)dr[colName];
            }
            if (dr[colAge] != null)
            {
                tmp.Age = (int)dr[colAge];
            }
            if (dr[colBrithDay] != null)
            {
                //tmp.BrithDay = (DateTime)dr[colBrithDay];
            }
            if (dr[colRemark] != null)
            {
                tmp.Remark = (string)dr[colRemark];
            }

            return tmp;
        }

        private List<test> FromDt(DataTable dt)
        {
            List<test> list = new List<test>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(From(dr));
            }
            return list;
        }



        public List<test> GetAll()
        {

            var command = dbDal.GetSqlStringCommond(GettSelectSQL());
            DataTable dt = dbDal.ExecuteDataTable(command);
            return FromDt(dt);
        }

        public test GetById(test tmp)
        {
            test tml = new test();

            var command = dbDal.GetSqlStringCommond(GetByIdSQL());
            AddIdentityColumn(command, tmp);
            DataTable dt = dbDal.ExecuteDataTable(command);
            List<test> list = FromDt(dt);
            if (list.Count > 0)
            {
                tml = list[0];
            }
            return tml;
        }



        private string GetByIdSQL()
        {
            var sql = string.Format(@"SELECT  {0}
                            FROM 
                                test
                            WHERE
                                {1}
                                       ", _columnCollectionSelectString, string.Format("{0} = @{0}", IdentityColumn));
            return sql;
        }
        public long Insert(test test)
        {
            var command = dbDal.GetSqlStringCommond(InsertSQL());
            var parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", IdentityColumn);
            parameter.DbType = DbType.Int64;
            parameter.Direction = ParameterDirection.Output;
            command.Parameters.Add(parameter);
            AddInsertParameters(command, test);
            dbDal.ExecuteNonQuery(command);
            var id = Convert.ToInt64(parameter.Value);
            return id;
        }

        public long Insert(test test, Trans t)
        {
            var command = dbDal.GetSqlStringCommond(InsertSQL());
            var parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", IdentityColumn);
            parameter.DbType = DbType.Int64;
            parameter.Direction = ParameterDirection.Output;
            command.Parameters.Add(parameter);
            AddInsertParameters(command, test);
            dbDal.ExecuteNonQuery(command, t);
            var id = Convert.ToInt64(parameter.Value);
            return id;
        }

        public void Update(test test)
        {
            var command = dbDal.GetSqlStringCommond(UpdatetSQL());
            AddUpdateParameters(command, test);
            dbDal.ExecuteNonQuery(command);
        }

        public void Update(test test, Trans t)
        {
            var command = dbDal.GetSqlStringCommond(UpdatetSQL());
            AddUpdateParameters(command, test);
            dbDal.ExecuteNonQuery(command, t);
        }

        public void Delete(test test)
        {
            var command = dbDal.GetSqlStringCommond(DeletetSQL());
            AddIdentityColumn(command, test);
            dbDal.ExecuteNonQuery(command);
        }

        public void Delete(test test, Trans t)
        {
            var command = dbDal.GetSqlStringCommond(DeletetSQL());
            AddIdentityColumn(command, test);
            dbDal.ExecuteNonQuery(command, t);
        }

        private void AddInsertParameters(DbCommand command, test test)
        {
            DbParameter parameter = null;
            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", colName);
            parameter.Value = test.Name;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", colAge);
            parameter.Value = test.Age;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", colBrithDay);
            parameter.Value = (object)test.BrithDay ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", colRemark);
            parameter.Value = (object)test.Remark ?? DBNull.Value;
            command.Parameters.Add(parameter);


        }

        private void AddUpdateParameters(DbCommand command, test test)
        {
            AddIdentityColumn(command, test);
            AddInsertParameters(command, test);
        }

        private void AddIdentityColumn(DbCommand command, test test)
        {

            var parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", IdentityColumn);
            parameter.Value = test.Id;
            command.Parameters.Add(parameter);

        }

        private string GettSelectSQL()
        {
            var sql = string.Format(@" SELECT  {0} 
                                          FROM 
                                                test
                                       ", _columnCollectionSelectString);
            return sql;
        }

        private string GetPagedQuery(string sqlText, int startIndex, int pageSize)
        {
            var endIndex = startIndex + pageSize;
            startIndex = startIndex + 1;

            var sql = string.Format(@"SELECT TOP {0} {4} 
						FROM ( 
							SELECT    {4} , ROW_NUMBER() OVER ({1}) AS ROW 
						    FROM  test ) AS WithRowNumber
					    WHERE
                            ROW BETWEEN {2} AND {3} {1}", pageSize, sqlText, startIndex, endIndex, _columnCollectionSelectString);

            return sql;
        }

        private string InsertSQL()
        {
            var nonIdentColumns = ColumnCollections.Except(new[] { IdentityColumn }).ToList();
            var sql = string.Format(@" INSERT INTO
                            test (
                              {0}
                            )
                            VALUES
                            (
                                {1}
                            ) ;
                                       ", nonIdentColumns.JoinFormat(",", "{0}"), nonIdentColumns.JoinFormat(",", "@{0}"));
            sql = string.Format("{0} SET @{1} = SCOPE_IDENTITY()", sql, IdentityColumn);
            return sql;
        }

        private string UpdatetSQL()
        {
            var sql = string.Format(@" UPDATE
                                 test
                            SET
                               {1}
                            WHERE 
                                  {0}
                                       ", WhereClause, ColumnCollections.Except(KeyColumns).ToArray().JoinFormat(" , ", "{0} = @{0}"));
            return sql;
        }

        private string DeletetSQL()
        {
            var sql = string.Format(@" DELETE  FROM
                              test
                            WHERE 
                                {0}           
                                       ", string.Format("{0}=@{0}", IdentityColumn));
            return sql;
        }

    }
}

 
